\c demo_tsearch postgres
\set ON_ERROR_STOP on

/*******************************************************************/
-- PART I: STANDARD QUERIES
drop table if exists mytable;
create table mytable(myword text, tsearch tsvector);
insert into mytable values ('dark'),('darkness'),('darked'),('darks'),('DARK'),('goat'),('tin can'),('dark sun'),('DarkAges'),('darkage'),('my dog sunshine');

update mytable set tsearch=to_tsvector(myword);

-- EX: standard SQL query
\H \o '02_ex1.html'

\qecho 'select myword from mytable;'
select myword from mytable;

\qecho 'select myword from mytable where myword =  \'dark\';'
select myword from mytable where myword =  'dark';

\qecho 'select myword from mytable where myword =  \'dark\' or myword=\'darkness\';'
select myword from mytable where myword =  'dark' or myword='darkness';

\qecho 'select myword from mytable where myword ~  \'dark\' or myword=\'darkness\';'
select myword from mytable where myword ~  'dark' or myword='darkness';

\qecho 'select myword from mytable where myword ~* \'dark\' or myword=\'darkness\';'
select myword from mytable where myword ~* 'dark' or myword='darkness';

\o '02_ex2.html'
-- EX: comparing the document and text_search columns
\qecho 'select myword,tsearch from mytable;'
select myword,tsearch from mytable;

-- EX: text search queries
\qecho 'select myword,tsearch from mytable where tsearch @@ to_tsquery(\'dark\');'
select myword,tsearch from mytable
  where tsearch @@ to_tsquery('dark');

\qecho 'select myword,tsearch from mytable where tsearch @@ to_tsquery(\'darks\');'
select myword,tsearch from mytable
  where tsearch @@ to_tsquery('darks');

\qecho 'select myword,tsearch from mytable where tsearch @@ to_tsquery(\'darkness\');'
select myword,tsearch from mytable
  where tsearch @@ to_tsquery('darkness');

/*******************************************************************/
-- PART II: TEXT SEARCH QUERIES

-- EX: ANALYZE standard query vs text search
\o '02_ex3.html'
\qecho 'explain analyze select content from book_time_machine where content ~* \'dark\' or content=\'darkness\';'
explain analyze
select content from book_time_machine where content ~* 'dark' or content='darkness';

\qecho 'explain analyze select content from book_time_machine where tsearch @@ to_tsquery(\'darkness\');'
explain analyze
select content from book_time_machine
  where tsearch @@ to_tsquery('darkness');

\qecho 'explain analyze select content from book_heart_darkness where content ~* \'dark\' or content=\'darkness\';'
explain analyze
select content from book_heart_darkness where content ~* 'dark' or content='darkness';

\qecho 'explain analyze select content from book_heart_darkness where tsearch @@ to_tsquery(\'darkness\');'
explain analyze
select content from book_heart_darkness
  where tsearch @@ to_tsquery('darkness');
